﻿using StackExchange.Redis;
using TeachlyQBank.DbPool;
using TeachlyQBank.Models;
using NLog;
using System.Data;
using System.Collections.Generic;
using System.Globalization;

namespace TeachlyQBank.DbServer
{
    public class ExamServer
    {
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly MySqlConnectionAdapter _mysql; // 操作mysql的类
        private readonly SqlServerConnectionAdapter _sqlServer;

        private readonly IConnectionMultiplexer _redis;  // 操作redis的类
        private readonly Logger _logger;  //记录日志的类

        // 完成依赖注入
        public ExamServer(MySqlConnectionAdapter mysql, IConnectionMultiplexer redis, IHttpContextAccessor httpContextAccessor, SqlServerConnectionAdapter sqlServer)
        {
            _httpContextAccessor = httpContextAccessor;
            _redis = redis;
            _logger = LogManager.GetCurrentClassLogger();
            _mysql = mysql;
            _sqlServer = sqlServer;
        }

        // 功能:向数据库提交创建新考试的请求
        // 参数:输入一个Exam类型的数据做为要创建的考试
        // 返回:执行成功返回数据库影响数据的条数1,失败返回-1
        public int AddExamServer(Exam exam)
        {
            _sqlServer.Open();
            string query = "insert into T_EMAM_PLAN(exam_plan_id, exam_plan_name, exam_plan_desc, teacher_id, begin_time, end_time,approval_status,paper_id) " +
                "values(@exam_plan_id, @exam_plan_name, @exam_plan_desc, @teacher_id, @begin_time, @end_time,'待审批',@paper_id)";

            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                { "@exam_plan_id", Guid.NewGuid() },
                { "@exam_plan_name", exam.exam_plan_name },
                { "@exam_plan_desc", exam.exam_plan_desc },
                { "@teacher_id", exam.teacher_id },
                { "@begin_time", exam.begin_time },
                { "@end_time", exam.end_time },
                {"@paper_id",exam.paper_id },    
            };


            int count = _sqlServer.ExecuteNonQuery(query, parameters);

            if (count > 0)
            {
                _logger.Info($"添加了一个新考试:{exam.exam_plan_name},考试描述:{exam.exam_plan_desc},本次花费了{_sqlServer.GetLastExecutionTime()}毫秒");
                _sqlServer.Close();
                return count;
            }
            else
            {
                _logger.Error($"添加考试出现错误,错误的sql语句:{_sqlServer.GetLastExecutedSql()},错误的信息:{_sqlServer.GetLastError()}");
                _sqlServer.Close();
                return -1;
            }
        }

        // 功能:根据一个考试的名称删除一个考试
        // 参数:传入一个exam里面包含了要删除考试的名称
        // 返回:执行成功返回数据库影响的条数1,失败返回-1
        public int DelExamServer(Exam exam)
        {
            _sqlServer.Open();
            string query = "DELETE FROM T_EMAM_PLAN WHERE exam_plan_name = @exam_plan_name";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                { "@exam_plan_name", exam.exam_plan_name }
            };
            int count = _sqlServer.ExecuteNonQuery(query, parameters);
            if (count > 0)
            {
                _logger.Info($"删除了一个考试:{exam.exam_plan_name},考试ID:{exam.exam_plan_id},本次花费了{_sqlServer.GetLastExecutionTime()}毫秒");
                _sqlServer.Close();
                return count;
            }
            else
            {
                _logger.Error($"删除考试出现错误,错误的sql语句:{_sqlServer.GetLastExecutedSql()},错误的信息:{_sqlServer.GetLastError()}");
                _sqlServer.Close();
                return -1;
            }
        }

        // 功能：用于查询数据库里的所有考试并展示在前端页面
        // 参数:不需要任何参数
        // 返回:查询成功返回一个List<Exam>包含了所有的考试信息，查询失败返回null
        public List<Exam> SltServer()
        {
            _sqlServer.Open();
            string query = "SELECT * FROM T_EMAM_PLAN";
            DataSet data = _sqlServer.ExecuteQuery(query);
            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                _sqlServer.Close();
                // 查询出错
                _logger.Error($"查询出错,错误的sql语句:{_sqlServer.GetLastExecutedSql()},错误的信息:{_sqlServer.GetLastError()}");
                return null;
            }
            else
            {
                List<Exam> resultList = new List<Exam>();
                foreach (DataRow row in data.Tables[0].Rows)
                {
                    Exam newExam = new Exam();
                    newExam.exam_plan_id = row["exam_plan_id"].ToString();
                    newExam.exam_plan_name = row["exam_plan_name"].ToString();
                    newExam.exam_plan_desc = row["exam_plan_desc"].ToString();
                    newExam.teacher_id = row["teacher_id"].ToString();
                    newExam.begin_time = DateTime.Parse(row["begin_time"].ToString());
                    newExam.end_time = DateTime.Parse(row["end_time"].ToString());
                    newExam.approval_status = row["approval_status"].ToString();
                    resultList.Add(newExam);
                }
                _sqlServer.Close();
                return resultList;
            }
        }

        // 功能：用于查询出需要修改的考试信息单独展示在一个页面
        // 参数：传入一个exam里面包含了要修改考试的名称
        // 返回:执行成功返回Exam对象，失败返回null
        public Exam SltUpdServer(Exam exam)
        {
            _sqlServer.Open();
            string query = "SELECT * FROM T_EMAM_PLAN WHERE exam_plan_name = @exam_plan_name";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                { "@exam_plan_name", exam.exam_plan_name }
            };
            DataSet data = _sqlServer.ExecuteQuery(query, parameters);
            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                _sqlServer.Close();
                // 查询出错
                _logger.Error($"查询出错,错误的sql语句:{_sqlServer.GetLastExecutedSql()},错误的信息:{_sqlServer.GetLastError()}");
                return null;
            }
            else
            {
                Exam exam1 = new Exam();
                foreach (DataRow row in data.Tables[0].Rows)
                {

                    exam1.exam_plan_id = row["exam_plan_id"].ToString();
                    exam1.exam_plan_name = row["exam_plan_name"].ToString();
                    exam1.exam_plan_desc = row["exam_plan_desc"].ToString();
                    exam1.teacher_id = row["teacher_id"].ToString();
                    exam1.begin_time = DateTime.Parse(row["begin_time"].ToString());
                    exam1.end_time = DateTime.Parse(row["end_time"].ToString());
                    exam1.exam_total_score = (int)row["exam_total_score"];
                    exam1.approval_status = row["approval_status"].ToString();
                    break;
                }
                _sqlServer.Close();
                return exam1;
            }
        }

        // 功能：用于修改考试信息
        // 参数：传入一个Exam对象包含了要修改的考试信息
        // 返回:执行成功返回数据库影响数据的条数1,失败返回-1
        public int UpdServer(Exam exam)
        {
            _sqlServer.Open();
            string query = "UPDATE T_EMAM_PLAN SET exam_plan_name = @exam_plan_name,exam_plan_desc = @exam_plan_desc,teacher_id = @teacher_id,begin_time = @begin_time,end_time = @end_time,exam_total_score = @exam_total_score,approval_status = @approval_status WHERE exam_plan_id = @exam_plan_id";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                { "@exam_plan_id", exam.exam_plan_id },
                { "@exam_plan_name", exam.exam_plan_name },
                { "@exam_plan_desc", exam.exam_plan_desc  },
                { "@teacher_id", exam.teacher_id },
                { "@begin_time", exam.begin_time },
                { "@end_time", exam.end_time },
                { "@exam_total_score", exam.exam_total_score },
                { "@approval_status", exam.approval_status },
            };
            int count = _sqlServer.ExecuteNonQuery(query, parameters);
            if (count > 0)
            {
                _logger.Info($"对:{exam.exam_plan_name}进行了更改,考试描述:{exam.exam_plan_desc},本次花费了{_sqlServer.GetLastExecutionTime()}毫秒");
                _sqlServer.Close();
                return count;
            }
            else
            {
                _logger.Error($"更改考试出现错误,错误的sql语句:{_sqlServer.GetLastExecutedSql()},错误的信息:{_sqlServer.GetLastError()}");
                _sqlServer.Close();
                return -1;
            }
        }

        // 功能：根据考试名称模糊查询考试信息
        // 参数：传入一个exam对象包含了模糊查询的考试名称
        // 返回:执行成功返回List<Exam>包含了查询到的考试信息，查询失败返回null
        public List<Exam> Slt2Server(Exam exam)
        {
            _sqlServer.Open();
            string query = "SELECT * FROM T_EMAM_PLAN WHERE exam_plan_name LIKE @exam_plan_name";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                { "@exam_plan_name", "%" + exam.exam_plan_name + "%" },    
            };
            DataSet data = _sqlServer.ExecuteQuery(query, parameters);
            _sqlServer.Close();
            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                // 查询出错
                _logger.Error($"查询出错，错误的SQL语句：{_sqlServer.GetLastExecutedSql()}，错误的信息：{_sqlServer.GetLastError()}");
                return null;
            }
            List<Exam> resultList = new List<Exam>();
            foreach (DataRow row in data.Tables[0].Rows)
            {
                Exam exam1 = new Exam();
                exam1.exam_plan_id = row["exam_plan_id"].ToString();
                exam1.exam_plan_name = row["exam_plan_name"].ToString();
                exam1.exam_plan_desc = row["exam_plan_desc"].ToString();
                exam1.teacher_id = row["teacher_id"].ToString();
                exam1.begin_time = DateTime.Parse(row["begin_time"].ToString());
                exam1.end_time = DateTime.Parse(row["end_time"].ToString());
                exam1.exam_total_score = (int)row["exam_total_score"];
                exam1.approval_status = row["approval_status"].ToString();
                resultList.Add(exam1);
            }
            return resultList;
        }
    }
}
